import pandas as pd
import numpy as np
from src.models.database import db, HistoricalData
from datetime import datetime
import io
import os

class DataService:
    """数据管理服务"""
    
    def __init__(self):
        self.supported_formats = ['.xlsx', '.xls', '.csv']
    
    def import_historical_data(self, file):
        """导入历史数据 - 支持新的简化格式"""
        try:
            # 检查文件格式
            filename = file.filename.lower()
            if not any(filename.endswith(fmt) for fmt in self.supported_formats):
                raise ValueError(f"不支持的文件格式，支持格式: {', '.join(self.supported_formats)}")
            
            # 读取文件数据
            file_content = file.read()
            
            if filename.endswith('.csv'):
                df = pd.read_csv(io.BytesIO(file_content), encoding='utf-8', header=None)
            else:
                df = pd.read_excel(io.BytesIO(file_content), header=None)
            
            # 解析特定格式的Excel数据
            parsed_data = self._parse_business_data(df)
            
            # 批量插入数据
            imported_count = 0
            updated_count = 0
            
            for record_data in parsed_data:
                # 检查是否已存在
                existing = HistoricalData.query.filter_by(
                    business_unit=record_data['business_unit'],
                    metric_name=record_data['metric_name'],
                    year=record_data['year']
                ).first()
                
                if existing:
                    # 更新现有记录
                    existing.value = record_data['value']
                    existing.unit = record_data['unit']
                    existing.updated_at = datetime.utcnow()
                    updated_count += 1
                else:
                    # 创建新记录
                    record = HistoricalData(
                        business_unit=record_data['business_unit'],
                        metric_name=record_data['metric_name'],
                        year=record_data['year'],
                        value=record_data['value'],
                        unit=record_data['unit']
                    )
                    db.session.add(record)
                    imported_count += 1
            
            db.session.commit()
            
            # 计算同比增长率
            self._calculate_growth_rates()
            
            return {
                'imported_count': imported_count,
                'updated_count': updated_count,
                'total_records': len(parsed_data)
            }
            
        except Exception as e:
            db.session.rollback()
            raise e
    
    def _parse_business_data(self, df):
        """解析业务数据Excel格式"""
        parsed_data = []
        
        # 业务单元映射
        business_units = {
            '集团合计': '集团合计',
            '人药制剂板块': '人药制剂板块',
            '人药原料板块': '人药原料板块',
            '植保板块': '植保板块',
            '动保板块': '动保板块'
        }
        
        # 指标映射
        metrics = {
            '销售收入': '销售收入',
            '净利润': '净利润',
            '净利润率%': '净利润率%'
        }
        
        # 年份列（从第3列开始：2021年, 2022年, 2023年, 2024年）
        years = [2021, 2022, 2023, 2024]
        year_columns = [2, 3, 4, 5]  # 对应Excel中的列索引
        
        current_business_unit = None
        
        # 遍历数据行
        for index, row in df.iterrows():
            if index < 1:  # 跳过标题行
                continue
                
            # 获取第一列的值（业务单元）
            first_col = str(row.iloc[0]).strip() if pd.notna(row.iloc[0]) else ''
            
            # 检查是否是业务单元行
            if first_col in business_units:
                current_business_unit = business_units[first_col]
                
            # 获取第二列的值（指标名称）
            second_col = str(row.iloc[1]).strip() if pd.notna(row.iloc[1]) else ''
            
            # 如果有当前业务单元和有效指标
            if current_business_unit and second_col in metrics:
                metric_name = metrics[second_col]
                
                # 获取各年份的数据
                for i, year in enumerate(years):
                    col_index = year_columns[i]
                    if col_index < len(row):
                        value_str = str(row.iloc[col_index]).strip()
                        
                        # 尝试转换为数值
                        try:
                            value = float(value_str)
                            
                            # 确定单位
                            unit = '%' if '率%' in metric_name else '亿元'
                            
                            parsed_data.append({
                                'business_unit': current_business_unit,
                                'metric_name': metric_name,
                                'year': year,
                                'value': value,
                                'unit': unit
                            })
                        except (ValueError, TypeError):
                            # 跳过无法转换的值
                            continue
        
        return parsed_data
    
    def _calculate_growth_rates(self):
        """计算同比增长率和CAGR"""
        try:
            # 获取所有业务单元和指标的组合
            combinations = db.session.query(
                HistoricalData.business_unit,
                HistoricalData.metric_name
            ).distinct().all()
            
            for business_unit, metric_name in combinations:
                # 获取该组合的所有年份数据，按年份排序
                records = HistoricalData.query.filter_by(
                    business_unit=business_unit,
                    metric_name=metric_name
                ).order_by(HistoricalData.year).all()
                
                if len(records) < 2:
                    continue
                
                # 计算同比增长率
                for i in range(1, len(records)):
                    current_record = records[i]
                    previous_record = records[i-1]
                    
                    if previous_record.value != 0:
                        yoy_rate = ((current_record.value - previous_record.value) / previous_record.value) * 100
                        current_record.yoy_growth_rate = round(yoy_rate, 2)
                
                # 计算CAGR（复合年均增长率）
                if len(records) >= 2:
                    first_record = records[0]
                    last_record = records[-1]
                    years_diff = last_record.year - first_record.year
                    
                    if years_diff > 0 and first_record.value > 0:
                        cagr = (pow(last_record.value / first_record.value, 1/years_diff) - 1) * 100
                        
                        # 为所有记录设置相同的CAGR
                        for record in records:
                            record.cagr = round(cagr, 2)
            
            db.session.commit()
            
        except Exception as e:
            print(f"计算增长率时出错: {e}")
            db.session.rollback()
    
    def query_historical_data(self, filters=None):
        """查询历史数据"""
        try:
            query = HistoricalData.query
            
            if filters:
                if filters.get('business_unit'):
                    query = query.filter(HistoricalData.business_unit == filters['business_unit'])
                if filters.get('metric_name'):
                    query = query.filter(HistoricalData.metric_name == filters['metric_name'])
                if filters.get('year'):
                    query = query.filter(HistoricalData.year == int(filters['year']))
                if filters.get('start_year'):
                    query = query.filter(HistoricalData.year >= int(filters['start_year']))
                if filters.get('end_year'):
                    query = query.filter(HistoricalData.year <= int(filters['end_year']))
            
            records = query.order_by(
                HistoricalData.business_unit,
                HistoricalData.metric_name,
                HistoricalData.year
            ).all()
            
            return [record.to_dict() for record in records]
                    
        except Exception as e:
            raise e
    
    def get_business_units(self):
        """获取所有业务单元"""
        try:
            units = db.session.query(HistoricalData.business_unit).distinct().all()
            return [unit[0] for unit in units]
        except Exception as e:
            raise e
    
    def get_metrics(self):
        """获取所有指标"""
        try:
            metrics = db.session.query(HistoricalData.metric_name).distinct().all()
            return [metric[0] for metric in metrics]
        except Exception as e:
            raise e
    
    def get_data_statistics(self):
        """获取数据统计信息 - 新版本"""
        try:
            # 基础统计
            total_records = HistoricalData.query.count()
            total_business_units = db.session.query(HistoricalData.business_unit).distinct().count()
            total_metrics = db.session.query(HistoricalData.metric_name).distinct().count()
            
            # 年份范围
            year_stats = db.session.query(
                db.func.min(HistoricalData.year).label('min_year'),
                db.func.max(HistoricalData.year).label('max_year')
            ).first()
            
            # 最近更新时间
            latest_update = db.session.query(
                db.func.max(HistoricalData.updated_at)
            ).scalar()
            
            # 按年份统计记录数
            yearly_stats = db.session.query(
                HistoricalData.year,
                db.func.count(HistoricalData.id).label('count')
            ).group_by(HistoricalData.year).order_by(HistoricalData.year).all()
            
            # 按业务单元统计
            business_unit_stats = db.session.query(
                HistoricalData.business_unit,
                db.func.count(HistoricalData.id).label('count')
            ).group_by(HistoricalData.business_unit).order_by(
                db.func.count(HistoricalData.id).desc()
            ).all()
            
            return {
                'total_records': total_records,
                'total_business_units': total_business_units,
                'total_metrics': total_metrics,
                'year_range': {
                    'min_year': year_stats.min_year if year_stats else None,
                    'max_year': year_stats.max_year if year_stats else None
                },
                'latest_update': latest_update.isoformat() if latest_update else None,
                'yearly_distribution': [
                    {'year': stat.year, 'count': stat.count}
                    for stat in yearly_stats
                ],
                'business_unit_distribution': [
                    {'business_unit': stat.business_unit, 'count': stat.count}
                    for stat in business_unit_stats
                ]
            }
            
        except Exception as e:
            raise e

